package Dao.impl;

import Dao.BuyTicketsDao;
import entity.Orders;
import entity.Seat;
import entity.Ticket;
import entity.Train;
import untils.Msg;
import untils.SqlCollection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BuyTicketsDaoImpl implements BuyTicketsDao {
    private SqlCollection sqlCollection = new SqlCollection();

    @Override
    public ArrayList<Train> findTickesData(String startStation, String endStation, String time ,int carStation)  {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<Train> trains = new ArrayList<>();
        Train train = null;
        Seat seat = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "SELECT * FROM trains inner join seat on trains.start_station LIKE ? AND trains.end_station LIKE ?  AND trains.train_id = seat.train_id and car_status = ? OR trains.departure_time LIKE ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, "%" + startStation + "%");
            pst.setString(2, "%" + endStation + "%");
            pst.setString(4, "%" + time + "%");
            pst.setInt(3,carStation);

            rs = pst.executeQuery();
            while (rs.next()) {
                train = new Train();
                seat = new Seat();
                train.setTrainId(rs.getInt(1));
                train.setTrainName(rs.getString(2));
                train.setDepartureTime(rs.getDate(3));
                train.setArrivalTime(rs.getDate(4));
                train.setStartStation(rs.getString(5));
                train.setEndStation(rs.getString(6));
                train.setCarStatus(rs.getString(7));
                train.setTicket_status(rs.getInt(8));
                seat.setSeatId(rs.getInt(9));
                seat.setTrainId(rs.getInt(10));
                seat.setType(rs.getString(11));
                seat.setPrice(rs.getString(12));
                seat.setNumber(rs.getInt(13));
                train.setSeat(seat);
                trains.add(train);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return trains;
    }

    @Override
    public Msg<Ticket> updateTicketsData(int userId, String idCard,Train train, String seatNumber) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        Msg msg = new Msg();
        try {
            conn = sqlCollection.openConnection();
            String sql = "insert into tickets(train_id,user_id,seat_type,seat_number,purchase_time,idcard) values (?,?,?,?,now(),?)";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, train.getTrainId());
            pst.setInt(2, userId);
            pst.setString(3, train.getSeat().getType());
            pst.setString(4, seatNumber);
            pst.setString(5,idCard);
//            pst.setDate(5, (java.sql.Date) date);
            int res = pst.executeUpdate();
            if (res > 0) {
                Msg.msg = "SUCCEED";
            }

        }  finally {
            sqlCollection.closeConnection(pst);
        }
        return msg;
    }

    @Override
    public int findSeatNumberFromTickets(String seatNumber, int trainId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;

        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from tickets where seat_number = ? and train_id=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, seatNumber);
            pst.setInt(2, trainId);
            rs = pst.executeQuery();
            int res = 0;
            while (rs.next()) {
                res = rs.getInt(1);
            }
            return res;
        } finally {
            sqlCollection.closeConnection(pst, rs);
        }
    }

    @Override
    public void findBuyState(int trainId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "SELECT * from trains where train_id = ? and ticket_status = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, trainId);
            pst.setInt(2, 1);
            rs = pst.executeQuery();
            int res = 0;
            while (rs.next()) {
                res = rs.getInt(1);
            }
            if (res > 0) {
                Msg.msg = "SUCCEED";
            } else {
                Msg.msg = "ERROR";
            }
        } finally {
            sqlCollection.closeConnection(pst, rs);
        }

    }

    @Override
    public void findTicketStation(int seatId) throws SQLException{
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "SELECT * from seat where seat_id = ? and ticket_status = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, seatId);
            pst.setInt(2, 1);
            rs = pst.executeQuery();
            int res = 0;
            while (rs.next()) {
                res = rs.getInt(6);
            }
            if (res > 0) {
                Msg.msg = "SUCCEED";
            } else {
                Msg.msg = "ERROR";
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            sqlCollection.closeConnection(pst, rs);
        }

    }

    @Override
    public int findBuyTickets(String idCard, int trainId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        int ticketId = 0;
        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from tickets where idcard = ? and train_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, idCard);
            pst.setInt(2, trainId);
            rs = pst.executeQuery();

            while (rs.next()) {
                ticketId = rs.getInt(1);
            }
            if (ticketId>0) {
                Msg.msg = "您已经购票请查看你的车票信息";
            } else {
                Msg.msg = "未购票";
            }
        }finally {
            sqlCollection.closeConnection(pst,rs);
        }
        return ticketId;
    }

    @Override
    public Msg<Orders> findBuyRecord(int user_id, int trainId,int ticketId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        Msg<Orders> order = new Msg<>();
        Orders data = new Orders();
        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from orders where user_id = ? and train_id = ? and ticket_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, user_id);
            pst.setInt(2, trainId);
            pst.setInt(3,ticketId);
            rs = pst.executeQuery();
            if (rs.next()) {
                 // 初始化Orders对象
                data.setOrderId(rs.getInt(1));
                data.setTicketId(rs.getInt(2));
                data.setUserId(rs.getInt(3));
                data.setTotalPrice(rs.getString(4));
                data.setOrderTime(rs.getDate(6));
                data.setStatus(rs.getString(8));
            }
            order.setData(data); // 设置数据到Msg对象
            if (order.getData() != null && order.getData().getTicketId() > 0) {
                Msg.msg = "SUCCEED";
            } else {
                Msg.msg = "未购票";
            }
        }finally {
            sqlCollection.closeConnection(pst,rs);
        }
        return order;
    }

    @Override
    public void updateTicketNumber(int seatId) throws Exception {
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            //1.写sql
            String sql = "update seat set ticket_number = ticket_number-1 where seat_id = ?";
            //2.创建预编译对象
            pst = conn.prepareStatement(sql);
            //3.给占位符赋值
            pst.setInt(1, seatId);
            //4.执行sql
            int res = pst.executeUpdate();
            //5.处理结果集
            if (res > 0) {
                Msg.msg = "SUCCEED";
            } else {
                Msg.msg = "ERROR";
            }

        } finally {
            sqlCollection.closeConnection(pst);
        }
    }

    @Override
    public void updateOrderData(int userId, Train train, int ticketId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "insert into orders(ticket_id,user_id,train_id,total_price,order_time,status) values (?,?,?,?,now(),?)";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, ticketId);
            pst.setInt(2, userId);
            pst.setInt(3,train.getTrainId());
            pst.setString(4, train.getSeat().getPrice()); // 修复：将错误的索引3改为4
            pst.setString(5, "未支付");
            int res = pst.executeUpdate();
            if (res > 0) {
                Msg.msg = "SUCCEED";
            }
        } finally {
            sqlCollection.closeConnection(pst);
        }
    }
    @Override
    public Msg<Orders> findOrderStation(int userId, int ticketId, int trainId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        Msg<Orders> msg = new Msg<>();
        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from orders where user_id = ? and ticket_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, userId);
            pst.setInt(2, ticketId);
            rs = pst.executeQuery();
            if (rs.next()) {
                Orders data = new Orders(); // 初始化Orders对象
                data.setOrderId(rs.getInt(1));
                data.setTicketId(rs.getInt(2));
                data.setUserId(rs.getInt(3));
                data.setTrain_id(rs.getInt(4));
                data.setTotalPrice(rs.getString(5));
                data.setOrderTime(rs.getDate(6));
                data.setStatus(rs.getString(7));
                msg.setData(data); // 设置数据到msg对象
            }
        } finally {
            sqlCollection.closeConnection(pst,rs);
        }
        return msg;
    }
    @Override
    public int findTicketId(String idCard, int trainId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;

        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from tickets where idcard = ? and train_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, idCard);
            pst.setInt(2, trainId);

            rs = pst.executeQuery();
            int ticketId = 0;
            while (rs.next()) {
                ticketId = rs.getInt(1);
            }
            return ticketId;
        } finally {
            sqlCollection.closeConnection(pst);
        }
    }

    @Override
    public void paymentOk(Orders order) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;

        try {
            conn = sqlCollection.openConnection();
            String sql = "update orders set status = ? where order_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,"已支付");
            pst.setInt(2,order.getOrderId());
            int res = pst.executeUpdate();
            if (res>0){
                Msg.msg = "SUCCEED";
            }else {
                Msg.msg = "ERROR";
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            sqlCollection.closeConnection(conn,pst);
        }
    }

    @Override
    public Train findTrainData(int trainId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        Train train = new Train();
        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from trains where train_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,trainId);
            rs = pst.executeQuery();

            if (rs.next()) { // 检查是否有数据行
                train.setDepartureTime(rs.getDate(3));
                train.setCarStatus(rs.getString(7));
            }

        }catch (Exception e){
            e.printStackTrace();
        }
        return train;
    }

    @Override
    public void deleteTickets(int ticketId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "update tickets set logic_delete = ? where ticket_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,1);
            pst.setInt(2,ticketId);
            int res = pst.executeUpdate();
            if (res>0){
                Msg.msg = "SUCCEED";
            }else {
                Msg.msg = "ERROR";
            }
        }finally {
            sqlCollection.closeConnection(pst);
        }
    }

    @Override
    public void deleteOrder(int ticketId) throws Exception {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "delete from orders where ticket_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,ticketId);
            int res = pst.executeUpdate();
            if (res>0){
                Msg.msg = "SUCCEED";
            }else {
                Msg.msg = "ERROR";
            }
        }finally {
            sqlCollection.closeConnection(pst);
        }
    }

    @Override
    public int findOrder(int ticketId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        int orderId = 0;
        try {
            conn = sqlCollection.openConnection();
            String sql = "select * from orders where ticket_id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,ticketId);
            rs = pst.executeQuery();

            while (rs.next()){
                orderId = rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return orderId;
    }

    @Override
    public void addSeatTicket(int trainId, String seatType) throws Exception{
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = sqlCollection.openConnection();
            String sql = "update seat set ticket_number = ticket_number+1 where train_id = ? where type = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,trainId);
            pst.setString(2,seatType);
            int res = pst.executeUpdate();
            if (res>0){
                Msg.msg = "退票成功";
            }else {
                Msg.msg = "ERROR";
            }
        }finally {
            sqlCollection.closeConnection(pst);
        }
    }
}
